package DBLayer;

import java.util.ArrayList;
import ModelLayer.Invoice;

import java.sql.*;

public class DBInvoice implements IFDBInvoice {
	private Connection con = DBConnection.getInstance().getDBcon();

	public DBInvoice() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<Invoice> getAllInvoices() {
		return miscWhere("");
	}

	@Override
	public Invoice getInvoiceNo(int invoiceNo) {
		String wClause = " id = '" + invoiceNo + "'";
		return singleWhere(wClause);
	}

	@Override
	public int insertInvoice(Invoice inv) throws Exception {
		//call to get the next id number
		int nextID = GetMax.getMaxId("Select max(id) from invoice");
		nextID = nextID + 1;
		System.out.println("next id = " +  nextID);
		int rc = -1;
		String query="INSERT INTO Invoice(invoiceNo, paymentDate, TotalInvoicePrice)  VALUES('" +
				nextID  + "','" +
				inv.getInvoiceNo()  + "','"  +
				inv.getPaymentDate()  + "','"  +
				inv.getTotalInvoicePrice() + "','" ;


		System.out.println("insert : " + query);
		try{ // insert new supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try
		catch(SQLException ex){
			System.out.println("Invoice not created");
			throw new Exception ("Invoice is not inserted correct");
		}
		return(rc);
	}

	@Override
	public int updateInvoice(Invoice inv) {

		Invoice invObj  = inv;
		int rc=-1;

		String query="UPDATE invoice SET "+
				"invoiceNo ='"+ invObj.getInvoiceNo()+"', "+
				"paymentDate ='"+ invObj.getPaymentDate() + "', " +
				"totalInvoicePrice ='"+ invObj.getTotalInvoicePrice() + "', " +
				" WHERE invoiceNo = '"+ invObj.getInvoiceNo() + "'";
		System.out.println("Update query:" + query);
		try{ // update employee
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//end try
		catch(Exception ex){
			System.out.println("Update exception in employee db: "+ex);
		}
		return(rc);
	}

	@Override
	public int deleteInvoice(Invoice inv) {
		int rc=-1;

		String query="DELETE FROM invoice WHERE id = '" +
				inv.getInvoiceNo() + "'";
		System.out.println(query);
		try{ // delete from supplier
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Delete exception in PartOrder db: "+ex);
		}
		return(rc);
	}

	private ArrayList<Invoice> miscWhere(String wClause)
	{
		ResultSet results;
		ArrayList<Invoice> list = new ArrayList<Invoice>();	

		String query =  buildQuery(wClause);

		try{ // read the supplier from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);


			while( results.next() ){
				Invoice invObj = new Invoice();
				invObj = buildInvoice(results);	
				list.add(invObj);	
			}//end while
			stmt.close();  

		}//end try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	private Invoice singleWhere(String wClause)
	{
		ResultSet results;
		Invoice invObj = new Invoice();

		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the product from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				invObj = buildInvoice(results);
				stmt.close();
			}
			else{ //no product was found
				invObj = null;
			}
		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return invObj;
	}

	private String buildQuery(String wClause)
	{
		String query="SELECT partOrderId, partPrice, amount, productId, salesOrderId  FROM PartOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	private Invoice buildInvoice(ResultSet results)
	{   Invoice invObj = new Invoice();
	try{ // the columns from the table supplier are used
		invObj.setInvoiceNo(results.getInt("invoiceNo"));
		invObj.setPaymentDate(results.getString("paymentDate"));
		invObj.setTotalInvoicePrice(results.getDouble("totalInvoicePrice"));
	}
	catch(Exception e)
	{
		System.out.println("error in building the Invoice object");
	}
	return invObj;
	}

}


